package com.connection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Main {

	public static void main(String[] args) {

		Main main=new Main();

		//main.insertRow("Vaibhav ",24,25000);
           main.selectRows();





	}



	public void updateRow(){

		Connection connection=	   DbUtils.getConnection();

		if(null!= connection){




		}



	}

	public void insertRow(String name,int age,float salary){

		Connection connection=	   DbUtils.getConnection();

		if(null!= connection){

			try {
				
				connection.setAutoCommit(false);
				/*Statement statement=	  connection.createStatement();
				//query will be compiled every time when executes
				String query="INSERT INTO EMPLOYEE VALUES(default,'RAJEEV SHUKLA',40000,23)";
				
			int b=	statement.executeUpdate(query);

			  if(b!=0){
				  System.out.println(b +" rows reflected..");
			  }*/
			  
			  
			  PreparedStatement preparedStatement=connection.prepareStatement("INSERT INTO EMPLOYEE VALUES(default,?,?,?)",PreparedStatement.RETURN_GENERATED_KEYS);
			 
			  preparedStatement.setString(1, name);
			  preparedStatement.setFloat(2, salary);
			  preparedStatement.setInt(3, age);
			  
			int b=  preparedStatement.executeUpdate();
			   if(b!=0){
				   System.out.println(b+" No of rows inserted..");
			   }
			  preparedStatement.getGeneratedKeys();
			   connection.commit();
			   
			  
			} catch (SQLException e) {
 
				 try {
					connection.rollback();
					connection.commit();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				
				e.printStackTrace();
			} finally {
				 try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}


		}


	}

	public void deleteRow(){

		Connection connection=	   DbUtils.getConnection();

		if(null!= connection){




		}


	}

	public void selectRows(){
		Connection connection=	   DbUtils.getConnection();

		if(null!= connection){

         try {
			PreparedStatement  ps=connection.prepareStatement("SELECT * FROM EMPLOYEE",ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.TYPE_FORWARD_ONLY);
			/* ps.setInt(1, 1);
			 */
	ResultSet	 rs=	   ps.executeQuery();
			  
	
	         while (rs.next()) {
	        	int id=rs.getInt(1);
	        	 
	       String empName= 	  rs.getString("EMP_NAME");
				
	           float salary=rs.getFloat("EMP_SALARY");
	            System.out.println("Id :"+id);
	            System.out.println("Name :"+empName);
	            System.out.println("Salary :"+salary);
	        	 
			}
			 
	         
	          System.out.println("------------------Reversing------------");
			 while (rs.previous()) {
				 int id=rs.getInt(1);
	        	 
			       String empName= 	  rs.getString("EMP_NAME");
						
			           float salary=rs.getFloat("EMP_SALARY");
			            System.out.println("Id :"+id);
			            System.out.println("Name :"+empName);
			            System.out.println("Salary :"+salary);
			       	
				 
			}
			 
			 
			 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


		}



	}



}
